﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using WindowsFormsApp1.BLL;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApp1
{
	public partial class MercadolivreData : Form
	{
		public MercadolivreData()
		{
			InitializeComponent();
			DataTable dt = ProductBLL.GetList("","", "Prouduct1");
			dataGridView1.AutoGenerateColumns = false;
			dataGridView1.DataSource = dt;

			this.listBox1.DisplayMember = "CategoryClassID";
			this.listBox1.ValueMember = "CategoryClassID";
			DataTable dt1 = ProductBLL.GetOneClass();
			this.listBox1.DataSource = dt1; 


			DataGridViewImageCell cell = new DataGridViewImageCell();
			dataGridView1.RowTemplate.Height = 100;
			cell.ImageLayout = DataGridViewImageCellLayout.Zoom;
			dataGridView1.Columns["PicUrl1"].CellTemplate = cell; 
			dataGridView1.Columns["PicUrl2"].CellTemplate = cell;
			dataGridView1.Columns[dataGridView1.Columns.Count - 1].Visible = false;//设置最后一列不可见  
		}

		private void button1_Click(object sender, EventArgs e)
		{
			string strWhere = "";  
			string s = "";
			int j = 0;
			for (j = 0; j < listBox1.SelectedItems.Count; j++)
			{
				if (j == listBox1.SelectedItems.Count - 1)
				{

					s += "'" + ((DataRowView)listBox1.SelectedItems[j])[0].ToString()+"'";
				}
				else
				{
					s += "'" + ((DataRowView)listBox1.SelectedItems[j])[0].ToString() + "', ";
				}
			}
			
			if (!string.IsNullOrWhiteSpace(s.ToString()))
			{
				strWhere += " CategoryClassID in(" + s + ")";
			}
			DataTable dt = ProductBLL.GetList(strWhere, textBox1.Text,textBox2.Text);
			dataGridView1.DataSource = dt;

		}

		private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
		{
			//自动编号，与数据无关
			Rectangle rectangle = new Rectangle(e.RowBounds.Location.X,
			   e.RowBounds.Location.Y,
			   dataGridView1.RowHeadersWidth - 4,
			   e.RowBounds.Height);
			TextRenderer.DrawText(e.Graphics,
				  (e.RowIndex + 1).ToString(),
				   dataGridView1.RowHeadersDefaultCellStyle.Font,
				   rectangle,
				   dataGridView1.RowHeadersDefaultCellStyle.ForeColor,
				   TextFormatFlags.VerticalCenter | TextFormatFlags.Right);
		}

		private void listBox1_MouseClick(object sender, MouseEventArgs e)
		{
			 
		}

		private void button2_Click(object sender, EventArgs e)
		{
			string strWhere = "";
			string s = "";
			int j = 0;
			for (j = 0; j < listBox1.SelectedItems.Count; j++)
			{
				if (j == listBox1.SelectedItems.Count - 1)
				{

					s += "'" + ((DataRowView)listBox1.SelectedItems[j])[0].ToString() + "'";
				}
				else
				{
					s += "'" + ((DataRowView)listBox1.SelectedItems[j])[0].ToString() + "', ";
				}
			}

			if (!string.IsNullOrWhiteSpace(s.ToString()))
			{
				strWhere += " CategoryClassID in(" + s + ")";
			}
			DataTable dt = ProductBLL.GetList(strWhere, textBox1.Text, textBox2.Text);
			ExportToExcel(dt, "daochu1.xls");
		}

		public void ExportToExcel(DataTable dt, string saveFileName)
		{
			if (dt == null) return;
			Excel.Application xlApp = new Excel.Application();
			if (xlApp == null)
			{
				// lblMsg.Text = "无法创建Excel对象，可能您的机子未安装Excel";
				MessageBox.Show("请确保您的电脑已经安装Excel", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Error);
				return;
			}
			Excel.Workbooks workbooks = xlApp.Workbooks;
			Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
			Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
			Excel.Range range = null;
			long totalCount = dt.Rows.Count;
			long rowRead = 0;
			float percent = 0;

			//写入标题
			for (int i = 0; i < dt.Columns.Count; i++)
			{
				worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
				range = (Excel.Range)worksheet.Cells[1, i + 1];
				range.Interior.ColorIndex = 15;//背景颜色
				range.Font.Bold = true; //粗体
				range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //居中
																		   //加边框
				range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin,
								   Excel.XlColorIndex.xlColorIndexAutomatic, null);
				//range.ColumnWidth = 4.63;//设置列宽
				//range.EntireColumn.AutoFit();//自动调整列宽
				//r1.EntireRow.AutoFit();//自动调整行高
			}
			//写入内容
			for (int r = 0; r < dt.Rows.Count; r++)
			{
				for (int i = 0; i < dt.Columns.Count; i++)
				{
					try
					{
						worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i];

						range = (Excel.Range)worksheet.Cells[r + 2, i + 1];
						range.Font.Size = 9; //字体大小
											 //加边框
						range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin,
										   Excel.XlColorIndex.xlColorIndexAutomatic, null);
						range.EntireColumn.AutoFit(); //自动调整列宽
					}
					catch (Exception)
					{
					}
				}
				rowRead++;
				percent = ((float)(100 * rowRead)) / totalCount;
				Application.DoEvents();
			}

			range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
			if (dt.Columns.Count > 1)
			{
				range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
			}

			try
			{
				workbook.Saved = true;

				SaveFileDialog SaveFile = new SaveFileDialog();
				SaveFile.FileName = saveFileName;
				//SaveFile.Filter = "Miscrosoft Office Excel 97-2003 工作表|*.xls|所有文件(*.*)|*.*";
				SaveFile.Filter = "Miscrosoft Office Excel 97-2003 工作表|*.xls|Excel 工作簿|*.xlsx|所有文件(*.*)|*.*";
				SaveFile.RestoreDirectory = true;
				if (SaveFile.ShowDialog() == DialogResult.OK)
				{
					workbook.SaveCopyAs(SaveFile.FileName);

					MessageBox.Show("导出数据成功!", "系统信息", MessageBoxButtons.OK, MessageBoxIcon.Information);
				}
			}
			catch (Exception ex)
			{
				//MessageBox.Show("导出文件时出错,文件可能正被打开!", "系统信息");
				MessageBox.Show(ex.Message.ToString());
			}


			workbooks.Close();
			if (xlApp != null)
			{
				xlApp.Workbooks.Close();

				xlApp.Quit();

				int generation = GC.GetGeneration(xlApp);
				System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);

				xlApp = null;
				GC.Collect(generation);
			}
			GC.Collect(); //强行销毁

			#region 强行杀死最近打开的Excel进程

			System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
			DateTime startTime = new DateTime();
			int m, killId = 0;
			for (m = 0; m < excelProc.Length; m++)
			{
				if (startTime < excelProc[m].StartTime)
				{
					startTime = excelProc[m].StartTime;
					killId = m;
				}
			}
		/*	if (excelProc[killId].HasExited == false)
			{
				excelProc[killId].Kill();
			}
			*/
			#endregion
		}

		private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
		{
			if (e.ColumnIndex == 7 || e.ColumnIndex == 8)
			{
				string path = e.Value.ToString();
				e.Value = GetImage(path);
			}
		}
		public System.Drawing.Image GetImage(string path)
		{
			//	System.IO.FileStream fs = new System.IO.FileStream(path, System.IO.FileMode.Open);
			//	System.Drawing.Image result = System.Drawing.Image.FromStream(fs);
			try
			{
				if (!string.IsNullOrWhiteSpace(path) && path!="0")
				{

					System.Drawing.Image result = Image.FromStream(System.Net.WebRequest.Create(path).GetResponse().GetResponseStream());
					return result;
				}
				else {
					return null;
				}
			}
			catch (Exception e)
			{

				throw;
			}


		}
	}
}
